package com.scalar.chiptrack.adaptor.ikanos.ase;

import com.scalar.chiptrack.jobs.JobHandler;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.InventoryLot;
import com.scalar.chiptrack.adaptor.TestLot;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.ShippedLot;
import com.scalar.chiptrack.adaptor.TestLotBinReport;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.adaptor.dao.TestAdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.InventoryLotDAO;
import com.scalar.chiptrack.adaptor.dao.ShippedLotDAO;
import com.scalar.chiptrack.adaptor.dao.AdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.AdaptorRunInfo;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.DateUtil;
import com.scalar.ScalarException;

import java.util.HashMap;
import java.util.ArrayList;
import java.util.Date;
import java.io.File;

/**
 * Created by IntelliJ IDEA.
 * User: munugoti
 * Date: 7 May, 2010
 * Time: 4:47:43 PM
 * To change this template use File | Settings | File Templates.
 */
public class ASESGDataParser extends JobHandler implements AdaptorConstants
{
    private HashMap<String, InventoryLot> m_preTestDataMap      = new HashMap<String, InventoryLot>();
    private HashMap<String, TestLot> m_ftDataMap                = new HashMap<String, TestLot>();
    private HashMap<String, InventoryLot> m_eolDataMap          = new HashMap<String, InventoryLot>();
    private HashMap<String, InventoryLot> m_fgDataMap           = new HashMap<String, InventoryLot>();
    private HashMap<String, ShippedLot> m_fgShipDataMap         = new HashMap<String, ShippedLot>();

    private Logger m_parserLogger   = null;
    private String m_dataOutputDir  = null;

    public ASESGDataParser() throws ScalarException
    {
        try
        {
            m_dataOutputDir = AdaptorUtils.getDataOutputDir( VENDOR_NAME_ASE_SG, LOCATION_TYPE_TEST);
            AdaptorUtils.makeAllAdaptorDirs( m_dataOutputDir );
            m_parserLogger = AdaptorUtils.getDataParserLogger( m_dataOutputDir,VENDOR_NAME_ASE_SG );
            TestAdaptorDAO.setLogger( m_parserLogger );
            InventoryLotDAO.setLogger(m_parserLogger);
            ShippedLotDAO.setLogger( m_parserLogger);
            AdaptorUtils.setLogger( m_parserLogger );
        }
        catch (ScalarException se)
        {
            log("ERROR!! ERROR!! ERROR!! Exception while loading ASE_SG WIP output directory details from database, Exception: " + se);
            throw se;
        }
    }
    /**
     *
     * @param args
     * @throws Exception
     */
    public static void main( String args[] ) throws Exception
    {
        try
        {
            new ASESGDataParser().execute();
        }
        catch (Exception e)
        {
            throw e;
        }
    }
    /**
     *
     * @throws Exception
     */
    public void execute() throws Exception
    {
        AdaptorUtils.printHeader(VENDOR_NAME_ASE_SG, CONSTANT_PARSER);

        File doneDestDir = new File(  m_dataOutputDir + "/done" );

        File sourceDataoutDir = new File( m_dataOutputDir );

        File[] fileList = sourceDataoutDir.listFiles();
        if ( ( fileList == null ) || ( fileList.length <= 3 ) )
        {
            log( "No file found in directory: " + sourceDataoutDir );
            throw new ScalarException( "\n+-----------No file found in directory " + sourceDataoutDir );
        }

        ArrayList<File> successFileList = new ArrayList<File>();

        boolean success = false;

        for ( int i = 0; i < fileList.length; i++ )
        {
            if ( fileList[i].isDirectory() == true )
            {
                continue;
            }

            try
            {
                // Step 1
                if ( fileList[i].getName() != null && fileList[i].getName().trim().startsWith( ASESGPOP3Adaptor.FILE_NAME_WIP ) )
                {
                    parseFTWipReport( fileList[i].getAbsolutePath() );

                    parseFGWipReport( fileList[i].getAbsolutePath() );

                    parseFGShipReport( fileList[i].getAbsolutePath() );

                    successFileList.add( fileList[i] );
                }
                if ( fileList[i].getName() != null && fileList[i].getName().trim().startsWith(ASESGPOP3Adaptor.FILE_NAME_FT_YIELD ) )
               {
                    ArrayList<TestLotBinReport> testBinList = parseFTYieldReport( fileList[i].getAbsolutePath() );

                    if ( testBinList != null && testBinList.size() > 0 )
                    {
                        //to get the SCS Test Bin Details
                        if ( testBinList.isEmpty() == false )
                        {
                            log( "Updating SCK Binning Information" );
                            TestAdaptorDAO.saveTestBinInfo ( null, testBinList , VENDOR_NAME_ASE_SG );
                        }
                        else
                        {
                            log( "ASE SG Binning Information List is Empty" );
                        }
                    }

                   successFileList.add( fileList[i] );
               }
                success = true;
            }
            catch ( Exception e )
            {
                success = false;
                log( "Exception while parsing and storing file: " + fileList[i].getAbsolutePath() + "\nException: " + e );
                e.printStackTrace();
                throw e;
            }
        }
                // to save the Class Stroe lot tables
        try
        {

            if ( m_preTestDataMap.isEmpty() == false )
            {
                log(" Updating Class Stroe Lot Information");
                InventoryLotDAO.saveInventoryLot( null, m_preTestDataMap.values().iterator(), LOCATION_TYPE_PRE_TEST, VENDOR_NAME_ASE_SG );
                success = true;
            }
            else
            {
                InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_PRE_TEST, VENDOR_NAME_ASE_SG);
            }
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log("ERROR!! ERROR!! ERROR!! Could not update Pre Test Lot information into database: " + se);
            throw se;
        }
        // Saving the Test Lot Information
        try
        {
            // Save Final Test Information
            if ( m_ftDataMap.isEmpty() == false )
            {
                log( " Updating Test Lot Information" );
                TestAdaptorDAO.saveTestLot( null, m_ftDataMap.values().iterator(), LOCATION_TYPE_FT, VENDOR_NAME_ASE_SG);
            }
            else
            {
                TestAdaptorDAO.closeTestLot( null, LOCATION_TYPE_FT, VENDOR_NAME_ASE_SG );
            }

            success = true;
            m_ftDataMap.clear();
            m_ftDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log( "ERROR!! ERROR!! ERROR!! Could not update Test Lot information into database: " + se );
            throw se;
        }

        try
        {
            if ( m_eolDataMap.isEmpty() == false )
            {
                log(" Updating EOL Class Stroe Lot Information");
                InventoryLotDAO.saveInventoryLot( null, m_eolDataMap.values().iterator(), LOCATION_TYPE_EOL, VENDOR_NAME_ASE_SG );
                success = true;
            }
            else
            {
                InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_EOL, VENDOR_NAME_ASE_SG  );
            }
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log("ERROR!! ERROR!! ERROR!! Could not update EOL Class Stroe Lot information into database: " + se);
            throw se;
        }

         // to save the Finished Goods lot tables
           try
           {
               if ( m_fgDataMap.isEmpty() == false )
               {
                   log(" Updating Finished Goods Lot Information");
                   InventoryLotDAO.saveInventoryLot( null, m_fgDataMap.values().iterator(), LOCATION_TYPE_FG, VENDOR_NAME_ASE_SG );
                   success = true;
               }
               else
               {
                   InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_FG, VENDOR_NAME_ASE_SG );
               }
          }
          catch (ScalarException se)
          {
              success = false;
              se.printStackTrace();
              log("ERROR!! ERROR!! ERROR!! Could not update Finished Goods Lot information into database: " + se);
              throw se;
          }

         try
        {
            // Save FG Shipping Information
            if ( m_fgShipDataMap.isEmpty() == false )
            {
                log( " Updating FG Shiping Lot Information" );
                ShippedLotDAO.saveShippedLot( null, m_fgShipDataMap.values().iterator() );
            }
            success = true;
            m_fgShipDataMap.clear();
            m_fgShipDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log( "ERROR!! ERROR!! ERROR!! Could not update FG Shipping Lot information into database: " + se );
            throw se;
        }
        // To Close the Drop Out Lots
        AdaptorDAO.closeDropOutLots(VENDOR_NAME_ASE_SG);

        // TO SEND THE NEW LOTS, NEW TRANSIT LOTS, NO PO LOTS, DISCREPANCY LOTS NOTIFICATIONS TO THE USER
        AdaptorDAO.sendAllNotifications(VENDOR_NAME_ASE_SG, true, true, true);

        int adaptor_id = AdaptorRunInfo.getLastRunAdaptorId( ASESGPOP3Adaptor.ADAPTOR_NAME, CONSTANT_ADAPTOR);

        // Update Adaptor_Run_Info table
        try
        {
            if ( success == true )
            {
                AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, ASESGPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
                System.out.println( "SUCCESSFULLY UPDATED IN THE DATABASE" );
            }
        }
        catch ( ScalarException se )
        {
            success = false; //As filES DATA SAVED , UPDATEING AS SUCCESS
            AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, ASESGPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
            log( "WARNING!! WARNING!! WARNING!! Could not update Adaptor run information, Exception: " + se );
            throw se;
        }
        // Move the successful files to 'done' directory
        if ( success )
        {
            AdaptorUtils.moveFilesToDone( successFileList, doneDestDir );
        }
    } //END OF EXCECUTE METHOD

    private void parseFTWipReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result            = null;
        TestLot testLot         = null;
        InventoryLot inventoryLot = null;
        String lotNumber        = null;
        String partNumber       = null;
        String fablotNumber     = null;
        try
        {
            File detailWipFile = new File( fileName );
            if ( detailWipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "\nParsing the file for FT Wip Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for FT Wip Report..." );

             Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(5);
            ((XLSParserImpl)parser).setDataStartRowIdx(6);

            result = parser.parse();

            while ( result.next() )
            {
                partNumber = result.getString("Mkt Part Number");
                fablotNumber = result.getString("Lot ID");
                lotNumber   =  result.getString("ASE Lot ID");
                if ( lotNumber == null )
                {
                    lotNumber = fablotNumber;
                }
                if (partNumber!= null && partNumber.trim().length()>0 && lotNumber != null && lotNumber.trim().length() > 0 
					&& !"Mkt Part Number".equalsIgnoreCase( partNumber.trim() ) )
                {
                    //PRE TEST
                    if ( result.getString("Pre-IQC") != null && !"0".equalsIgnoreCase( result.getString("Pre-IQC") )
                         || result.getString("Pre-Test") != null && !"0".equalsIgnoreCase( result.getString("Pre-Test") )   )
                    {
                        inventoryLot = (InventoryLot)m_preTestDataMap.get( partNumber + lotNumber + fablotNumber );

                        if ( inventoryLot == null )
                        {
                            inventoryLot = new InventoryLot( VENDOR_NAME_ASE_SG, LOCATION_TYPE_PRE_TEST );
                            m_preTestDataMap.put( partNumber + lotNumber + fablotNumber, inventoryLot );
                        }

                        if( result.getString("Pre-IQC") != null && !"0".equalsIgnoreCase( result.getString("Pre-IQC") ) )
                       {
                           inventoryLot = (InventoryLot)m_preTestDataMap.get( partNumber + lotNumber + fablotNumber );
                           if( inventoryLot.getQtyIn() != null )
                           {
                                try
                                {
                                    inventoryLot.setQtyIn( ( Integer.parseInt( inventoryLot.getQtyIn() ) + Integer.parseInt( result.getString("Pre-IQC") ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    inventoryLot.setQtyIn( result.getString("Pre-IQC") );
                                }
                            }
                            else
                            {
                                inventoryLot.setQtyIn( result.getString("Pre-IQC") );
                            }
                            if ( inventoryLot.getStage() != null && !"Pre-IQC".equalsIgnoreCase( inventoryLot.getStage() ) )
                            {
                                inventoryLot.setStage( inventoryLot.getStage() + ", Pre-IQC");
                            }
                            else
                            {
                                inventoryLot.setStage("Pre-IQC");
                            }
                        }
                        if( result.getString("Pre-Test") != null && !"0".equalsIgnoreCase( result.getString("Pre-Test") ) )
                        {
                           inventoryLot = (InventoryLot)m_preTestDataMap.get( partNumber + lotNumber + fablotNumber );
                           if( inventoryLot.getQtyIn() != null )
                           {
                                try
                                {
                                    inventoryLot.setQtyIn( ( Integer.parseInt( inventoryLot.getQtyIn() ) + Integer.parseInt( result.getString("Pre-Test") ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    inventoryLot.setQtyIn( result.getString("Pre-Test") );
                                }
                            }
                            else
                            {
                                inventoryLot.setQtyIn( result.getString("Pre-Test") );
                            }
                            if ( inventoryLot.getStage() != null && !"Pre-Test".equalsIgnoreCase( inventoryLot.getStage() ) )
                            {
                                inventoryLot.setStage( inventoryLot.getStage() + ", Pre-Test");
                            }
                            else
                            {
                                inventoryLot.setStage("Pre-Test");
                            }
                        }

                         if ( inventoryLot.getQtyIn() != null && !"0".equalsIgnoreCase(inventoryLot.getQtyIn() ) )
                         {
                            inventoryLot.setPartNumber( partNumber );
                            inventoryLot.setLotNumber( lotNumber );
                            if ( result.getDate("Date Received") != null )
                            {
                                inventoryLot.setDateIn( StringUtils.formatDateToString(result.getDate("Date Received") ) );
                            }
                            inventoryLot.setFabLotNumber( fablotNumber );
                            inventoryLot.setPkgType( result.getString("Package") );
                            inventoryLot.setLeadCount( result.getString("Lead Count") );
                            inventoryLot.setDateCode( result.getString("Date Code") );
                            inventoryLot.setStatus( LOT_STATUS_RUNNING );
                            inventoryLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );
                        }
                    }

                    // FT Process  PT_BUFF, PT, BI,T1,T2 ,TX,Hold
                    if ( result.getString("Testing") != null && !"0".equalsIgnoreCase( result.getString("Testing") )
                        || result.getString("Eng Hold") != null && !"0".equalsIgnoreCase( result.getString("Eng Hold") ) )
                    {
                        log(lotNumber + "In Test FT");

                        testLot = (TestLot) m_ftDataMap.get( partNumber + lotNumber + fablotNumber );
                        if (testLot == null)
                        {
                            testLot = new TestLot(VENDOR_NAME_ASE_SG);
                            m_ftDataMap.put(partNumber + lotNumber + fablotNumber, testLot);
                        }

                       // FOR SETTING THE QTY IN

                       if( result.getString("Testing") != null && !"0".equalsIgnoreCase( result.getString("Testing") ) )
                       {
                           testLot = (TestLot) m_ftDataMap.get( partNumber + lotNumber + fablotNumber );
                           if( testLot.getQtyIn() != null )
                           {
                                try
                                {
                                    testLot.setQtyIn( ( Integer.parseInt( testLot.getQtyIn() ) + Integer.parseInt( result.getString("Testing") ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    testLot.setQtyIn( result.getString("Testing") );
                                }
                            }
                            else
                            {
                                testLot.setQtyIn( result.getString("Testing") );
                            }
                            if ( testLot.getStage() != null && !"Testing".equalsIgnoreCase( testLot.getStage() ) )
                            {
                                testLot.setStage( testLot.getStage() + ", Testing");
                            }
                            else
                            {
                                testLot.setStage("Testing");
                            }
                        }
                        if( result.getString("Eng Hold") != null && !"0".equalsIgnoreCase( result.getString("Eng Hold") ) )
                        {
                           testLot = (TestLot) m_ftDataMap.get( partNumber + lotNumber + fablotNumber );
                           if( testLot.getQtyIn() != null )
                           {
                                try
                                {
                                    testLot.setQtyIn( ( Integer.parseInt( testLot.getQtyIn() ) + Integer.parseInt( result.getString("Eng Hold") ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    testLot.setQtyIn( result.getString("Eng Hold") );
                                }
                            }
                            else
                            {
                                testLot.setQtyIn( result.getString("Eng Hold") );
                            }
                            if ( testLot.getStage() != null && !"Eng Hold".equalsIgnoreCase( testLot.getStage() ) )
                            {
                                testLot.setStage( testLot.getStage() + ", Eng Hold");
                            }
                            else
                            {
                                testLot.setStage("Eng Hold");
                            }
                        }
                       
                        if ( testLot.getQtyIn() != null && !"0".equalsIgnoreCase(testLot.getQtyIn() ) )
                        {
                            testLot.setPartNumber( partNumber );
                            testLot.setLotNumber( lotNumber );
                            testLot.setFabLotNumber( fablotNumber );
                            if ( result.getDate("Test In Date") != null )
                            {
                                testLot.setDateIn( StringUtils.formatDateToString(result.getDate("Test In Date") ) );
                            }
                            else if (result.getDate("Date Received") != null)
                            {
                                testLot.setDateIn( StringUtils.formatDateToString(result.getDate("Date Received") ) );
                            }
                            
                            testLot.setLeadCount( result.getString("Lead Count") );
                            testLot.setPackageType( result.getString("Package") );
                            testLot.setDateCode( result.getString("Date Code"));
                            if ( result.getString("Eng Hold") != null && !"0".equalsIgnoreCase( result.getString("Eng Hold") ) )
                            {
                               testLot.setStatus(LOT_STATUS_HOLD);
                            }
                            else
                            {
                               testLot.setStatus(LOT_STATUS_RUNNING);
                            }
                            testLot.setPsType( LOCATION_TYPE_FT );
                            testLot.setCategory( LOT_CATEGORY_TYPE_PROD );
                        }
                    }

                    // EOL Process   TM,VM, BK, TR, PK, FT Else
                    if ( result.getString("Eng Hold (BE)") != null && !"0".equalsIgnoreCase( result.getString("Eng Hold (BE)") )
                        || result.getString("VM") != null && !"0".equalsIgnoreCase( result.getString("VM") )
                        || result.getString("BAKE") != null && !"0".equalsIgnoreCase( result.getString("BAKE") )
                        || result.getString("TNR") != null && !"0".equalsIgnoreCase( result.getString("TNR") )
                        || result.getString("Packing") != null && !"0".equalsIgnoreCase( result.getString("Packing") ) )
                    {
                        log(lotNumber + "In EOL");

                        inventoryLot = (InventoryLot) m_eolDataMap.get( partNumber + lotNumber + fablotNumber );
                        if (inventoryLot == null)
                        {
                            inventoryLot = new InventoryLot(VENDOR_NAME_ASE_SG, LOCATION_TYPE_EOL);
                            m_eolDataMap.put(partNumber + lotNumber + fablotNumber, inventoryLot);
                        }

                       // FOR SETTING THE QTY IN
                       if( result.getString("Eng Hold (BE)") != null && !"0".equalsIgnoreCase( result.getString("Eng Hold (BE)") ) )
                       {
                           inventoryLot = (InventoryLot) m_eolDataMap.get( partNumber + lotNumber + fablotNumber );
                           if( inventoryLot.getQtyIn() != null )
                           {
                                try
                                {
                                    inventoryLot.setQtyIn( ( Integer.parseInt( inventoryLot.getQtyIn() ) + Integer.parseInt( result.getString("Eng Hold (BE)") ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    inventoryLot.setQtyIn( result.getString("Eng Hold (BE)") );
                                }
                            }
                            else
                            {
                                inventoryLot.setQtyIn( result.getString("Eng Hold (BE)") );
                            }
                            if ( inventoryLot.getStage() != null && !"Eng Hold (BE)".equalsIgnoreCase( inventoryLot.getStage() ) )
                            {
                                inventoryLot.setStage( inventoryLot.getStage() + ", Eng Hold (BE)");
                            }
                            else
                            {
                                inventoryLot.setStage("Eng Hold (BE)");
                            }
                        }

                        if( result.getString("VM") != null && !"0".equalsIgnoreCase( result.getString("VM") ) )
                        {
                           inventoryLot = (InventoryLot) m_eolDataMap.get( partNumber + lotNumber + fablotNumber );
                           if( inventoryLot.getQtyIn() != null )
                           {
                                try
                                {
                                    inventoryLot.setQtyIn( ( Integer.parseInt( inventoryLot.getQtyIn() ) + Integer.parseInt( result.getString("VM") ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    inventoryLot.setQtyIn( result.getString("VM") );
                                }
                            }
                            else
                            {
                                inventoryLot.setQtyIn( result.getString("VM") );
                            }
                            if ( inventoryLot.getStage() != null && !"VM".equalsIgnoreCase( inventoryLot.getStage() ) )
                            {
                                inventoryLot.setStage( inventoryLot.getStage() + ", VM");
                            }
                            else
                            {
                                inventoryLot.setStage("VM");
                            }
                        }
                        if( result.getString("BAKE") != null && !"0".equalsIgnoreCase( result.getString("BAKE") ) )
                        {
                            inventoryLot = (InventoryLot) m_eolDataMap.get( partNumber + lotNumber + fablotNumber );
                            if( inventoryLot.getQtyIn() != null )
                            {
                                try
                                {
                                    inventoryLot.setQtyIn( ( Integer.parseInt( inventoryLot.getQtyIn() ) + Integer.parseInt( result.getString("BAKE") ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    inventoryLot.setQtyIn( result.getString("BAKE") );
                                }
                             }
                             else
                             {
                                inventoryLot.setQtyIn( result.getString("BAKE") );
                             }
                             if ( inventoryLot.getStage() != null && !"Baking".equalsIgnoreCase( inventoryLot.getStage()) )
                             {
                                inventoryLot.setStage( inventoryLot.getStage() + ", Baking");
                             }
                             else
                             {
                                inventoryLot.setStage("Baking");
                             }
                        }
                       if( result.getString("TNR") != null && !"0".equalsIgnoreCase( result.getString("TNR") ) )
                       {
                           inventoryLot = (InventoryLot) m_eolDataMap.get( partNumber + lotNumber + fablotNumber );
                           if( inventoryLot.getQtyIn() != null )
                           {
                                try
                                {
                                    inventoryLot.setQtyIn( ( Integer.parseInt( inventoryLot.getQtyIn() ) + Integer.parseInt( result.getString("TNR") ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    inventoryLot.setQtyIn( result.getString("TNR") );
                                }
                            }
                            else
                            {
                                inventoryLot.setQtyIn( result.getString("TNR") );
                            }
                            if ( inventoryLot.getStage() != null && !"TNR".equalsIgnoreCase( inventoryLot.getStage() ) )
                            {
                                inventoryLot.setStage( inventoryLot.getStage() + ", TNR");
                            }
                            else
                            {
                                inventoryLot.setStage("TNR");
                            }
                        }
                        if( result.getString("Packing") != null && !"0".equalsIgnoreCase( result.getString("Packing") ) )
                        {
                            inventoryLot = (InventoryLot) m_eolDataMap.get( partNumber + lotNumber + fablotNumber );
                            if( inventoryLot.getQtyIn() != null )
                            {
                                try
                                {
                                    inventoryLot.setQtyIn( ( Integer.parseInt( inventoryLot.getQtyIn() ) + Integer.parseInt( result.getString("Packing") ) ) + "" );
                                }
                                catch(Exception e)
                                {
                                    inventoryLot.setQtyIn( result.getString("Packing") );
                                }
                             }
                             else
                             {
                                inventoryLot.setQtyIn( result.getString("Packing") );
                             }
                             if ( inventoryLot.getStage() != null && !"Packing".equalsIgnoreCase( inventoryLot.getStage() ) )
                             {
                                inventoryLot.setStage( inventoryLot.getStage() + ", Paking");
                             }
                             else
                             {
                                inventoryLot.setStage("Paking");
                             }
                        }
                        
                        if ( inventoryLot.getQtyIn() != null && !"0".equalsIgnoreCase(inventoryLot.getQtyIn() ) )
                        {
                            inventoryLot.setPartNumber( partNumber );
                            inventoryLot.setLotNumber( lotNumber );
                            inventoryLot.setFabLotNumber( fablotNumber );
                            if ( result.getDate("Test Out Date") != null )
                            {
                                inventoryLot.setDateIn( StringUtils.formatDateToString(result.getDate("Test Out Date") ) );
                            }

                            inventoryLot.setPkgType( result.getString("Package") );
                            inventoryLot.setLeadCount( result.getString("Lead Count") );
                            if ( result.getString("Eng Hold (BE)") != null && !"0".equalsIgnoreCase( result.getString("Eng Hold (BE)") ) )
                            {
                               inventoryLot.setStatus(LOT_STATUS_HOLD);
                            }
                            else
                            {
                               inventoryLot.setStatus(LOT_STATUS_RUNNING);
                            }
                            inventoryLot.setDateCode( result.getString("Date Code"));
                            inventoryLot.setCategory( LOT_CATEGORY_TYPE_PROD );
                        }
                    } //END OF EOL
                } //Part number Null checking
            }
        }
        catch ( Exception ex )
        {
            System.out.println("ERROR!!!ERROR!!! While Parsing the ASESG FT Wip Report");
            ex.printStackTrace();
            throw ex;
        }
    } // end of prase FT Wip Report method

    private void parseFGWipReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result            = null;
        InventoryLot inventoryLot = null;
        String lotNumber        = null;
        String partNumber       = null;
        String fablotNumber     = null;
        try
        {
            File detailWipFile = new File( fileName );
            if ( detailWipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "\nParsing the file for FG Wip Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for FG Wip Report..." );

             Parser parser = ParserFactory.getXLSParser( detailWipFile, 1 );

            ((XLSParserImpl)parser).setHeaderRowIdx(5);
            ((XLSParserImpl)parser).setDataStartRowIdx(6);

            result = parser.parse();

            while ( result.next() )
            {
                partNumber = result.getString("Mkt Part Number");
                fablotNumber = result.getString("Lot ID");
                lotNumber   =  result.getString("ASE Lot ID");
                if ( lotNumber == null )
                {
                    lotNumber = fablotNumber;
                }
                if (partNumber!= null && partNumber.trim().length()>0 && lotNumber != null && lotNumber.trim().length() > 0 
					&& !"Mkt Part Number".equalsIgnoreCase( partNumber.trim() ) )
                {
                    if ( result.getInt(11) > 0 )
                    {
                        inventoryLot = (InventoryLot) m_fgDataMap.get( partNumber + lotNumber + fablotNumber);

                        if (inventoryLot == null)
                        {
                            inventoryLot = new InventoryLot(VENDOR_NAME_ASE_SG, LOCATION_TYPE_FG );
                            m_fgDataMap.put( partNumber + lotNumber + fablotNumber, inventoryLot);
                        }
                        int quantity = 0;
                        if (inventoryLot.getQtyIn() != null)
                        {
                            try
                            {
                                quantity = Integer.parseInt(inventoryLot.getQtyIn());
                            }
                            catch (NumberFormatException ignore) {
                            }
                            quantity += result.getInt(11);
                        }
                        else
                        {
                           quantity = result.getInt(11);
                        }

                        if ( quantity > 0 )
                        {
                            inventoryLot.setLotNumber( lotNumber );
                            inventoryLot.setPartNumber( partNumber );
                            inventoryLot.setStatus( LOT_STATUS_RUNNING );
                            inventoryLot.setFabLotNumber( fablotNumber );
                            inventoryLot.setCategory( LOT_CATEGORY_TYPE_PROD );
                            if (result.getDate("FG Date") != null )
                            {
                                inventoryLot.setDateIn( StringUtils.formatDateToString(result.getDate("FG Date") ) );
                            }

                            inventoryLot.setQtyIn( quantity +"");
                            inventoryLot.setDateCode( result.getString("Date Code") );
                            inventoryLot.setPkgType( result.getString("Package") );
                            inventoryLot.setLeadCount( result.getString("Lead Count") );
                        }
                    }
                } //Part number Null checking
            }
        }
        catch ( Exception ex )
        {
            System.out.println("ERROR!!!ERROR!!! While Parsing the ASESG FG Wip Report");
            ex.printStackTrace();
            throw ex;
        }
    } // end of prase FG Wip Report method

    private void parseFGShipReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result     = null;
        ShippedLot shippedLot   = null;
        String partNumber       = null;
        String lotNumber        = null;
        String fabLotNumber     = null;
        String shipDate         = null;
        String nextLocation     = null;
        String dropShipNo       = null;

        try
        {
            File shipFile = new File( fileName );
            if ( shipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "\nParsing the file for ASE SG Ship Info..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for ASE SG Ship Info..." );

            Parser parser = ParserFactory.getXLSParser( shipFile , 2 );

            ((XLSParserImpl)parser).setHeaderRowIdx(5);
            ((XLSParserImpl)parser).setDataStartRowIdx(6);

            result = parser.parse();

            while ( result.next() )
            {
                partNumber = result.getString("Mkt Part Number");
                lotNumber  = result.getString("Vendor Lot No");
                fabLotNumber = result.getString("LOT NUMBER");
                if ( lotNumber == null )
                {
                    lotNumber = fabLotNumber;
                }

                if ( partNumber != null && partNumber.trim().length() > 0 && lotNumber != null && lotNumber.trim().length() >0
					&& !"Mkt Part Number".equalsIgnoreCase( partNumber.trim() ) )
                {
                    if ( result.getDate("Ship Date") != null )
                    {
                        shipDate = StringUtils.formatDateToString ( result.getDate("Ship Date") );
                    }
                    nextLocation = result.getString("SHIP TO LOCATION");
                    int quantity = 0;
                    dropShipNo = result.getString("SHIP REQ NUMBER");

                    shippedLot = (ShippedLot) m_fgShipDataMap.get( partNumber + fabLotNumber+ lotNumber + shipDate + nextLocation + dropShipNo );

                    if (shippedLot == null)
                    {
                        shippedLot = new ShippedLot(VENDOR_NAME_ASE_SG, LOCATION_TYPE_FINISHED_GOODS );
                        m_fgShipDataMap.put(partNumber + fabLotNumber+ lotNumber + shipDate+ nextLocation + dropShipNo, shippedLot);
                    }
                    shippedLot.setProcessType( LOCATION_TYPE_FG );
                    if ( shippedLot.getShipQty() != null )
                    {
                       try
                       {
                           quantity = Integer.parseInt( shippedLot.getShipQty() );
                       }
                       catch ( NumberFormatException ignore )
                       {
                           quantity = 0;
                       }
                       quantity += result.getInt("Ship Qty");

                    }
                    else
                    {
                       quantity = result.getInt("Ship Qty");
                    }

                    if ( quantity > 0 )
                    {
                        shippedLot.setPartNumber( partNumber );
                        shippedLot.setShipDate( shipDate );
                        shippedLot.setShipQty( quantity + "" );
                        shippedLot.setLotNumber( lotNumber );
                        shippedLot.setShipQty( quantity + "");
                        shippedLot.setShippingDoc( dropShipNo );
                        shippedLot.setMawb( result.getString("MAWB NO") );
                        shippedLot.setHawb( result.getString("HAWB NO") );
                        shippedLot.setCustomerSO( result.getString("Sales Order No") );
                        shippedLot.setFlightNumber( result.getString("FLIGHT NO"));
                        shippedLot.setEta( StringUtils.formatDateToString( result.getDate("ETA") ));
                        shippedLot.setEtd( StringUtils.formatDateToString( result.getDate("ETD")) );
                        shippedLot.setNextLocation( nextLocation );
                        shippedLot.setFabLotNumber( fabLotNumber );
                    }
                }
            }
        }
        catch ( Exception ex )
        {
            System.out.println("ERROR!!!ERROR!!! While Parsing the ASE SG SHIP sheet");
            ex.printStackTrace();
            throw ex;
        }

    }// end of prase Shipment Report method

    private ArrayList<TestLotBinReport> parseFTYieldReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );
        ParserResult result     = null;

        String lotNumber        = null;
        String partNumber       = null;
        String fablotNumber     = null;
        ArrayList<TestLotBinReport> ftYieldList  = new ArrayList<TestLotBinReport>();

        try
        {
            File detailWipFile = new File( fileName );
            if ( detailWipFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "\nParsing the file for ASE SG Ft Yield Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for ASE SG FT Yield Report..." );

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(0);
            ((XLSParserImpl)parser).setDataStartRowIdx(1);

            result = parser.parse();

            //System.out.println("result.getString() = " + result.getHeadings() );

            while ( result.next() )
            {
                partNumber   = result.getString("DEVICE");
                fablotNumber = result.getString("Subcon Lot No");
                lotNumber    = result.getString("Ikanons Lot No");
                if ( lotNumber == null)
                {
                    lotNumber = fablotNumber;
                }

                if ( partNumber != null && partNumber.length() > 0 && lotNumber != null && lotNumber.length() > 0 )
                {
                    TestLotBinReport ftYieldStatus = new TestLotBinReport();

                    ftYieldStatus.setDeviceName( partNumber );
                    ftYieldStatus.setLotNumber( lotNumber );
                    ftYieldStatus.setCustLotNo( fablotNumber );
                    
                    ftYieldStatus.setPkgType( result.getString("Package") );
                    ftYieldStatus.setLeadCount( result.getString("L/C") );
                    ftYieldStatus.setStage( result.getString("INSERTION") ); // Insertion
                    ftYieldStatus.setTestProg(result.getString("Program No"));
                    ftYieldStatus.setTesterId( result.getString("Tester ID") );
                    ftYieldStatus.setDateCode( result.getString("Date Code") );
                    ftYieldStatus.setHandler( result.getString("Handler ID") );
                    ftYieldStatus.setLb(result.getString("L/B ID"));
                    ftYieldStatus.setTemp( result.getString("Temp"));
                    ftYieldStatus.setDateIn( StringUtils.formatDateToString(result.getDate("Test In Date"), "MM/dd/yyyy HH:mm:ss" ) );
                    ftYieldStatus.setDateOut( StringUtils.formatDateToString(result.getDate("Test Out Date"), "MM/dd/yyyy HH:mm:ss" ) );
                    ftYieldStatus.setInQty( result.getInt("In Qty") +"");
                    ftYieldStatus.setPassQty( result.getInt("Out Qty")+"" );

                    //ftYieldStatus.setYield( result.getDouble("Yield")+"".replaceAll("%","")); handled in DataBase side
                    ftYieldStatus.setBin1( result.getInt("Bin 1")+"" );
                    ftYieldStatus.setBin2(result.getInt("Bin 2")+"");
                    ftYieldStatus.setBin3(result.getInt("Bin 3")+"");
                    ftYieldStatus.setBin4(result.getInt("Bin 4")+"");
                    ftYieldStatus.setBin5(result.getInt("Bin 5")+"");
                    ftYieldStatus.setBin6(result.getInt("Bin 6")+"");
                    ftYieldStatus.setBin7(result.getInt("Bin 7")+"");
                    ftYieldStatus.setBin8(result.getInt("Bin 8")+"");

                    ftYieldStatus.setLossQty( result.getInt("Loss QTY")+"" );
                    ftYieldStatus.setVmQty( result.getInt("VM QTY")+"" );
                    ftYieldStatus.setOsQty( result.getInt("OS QTY")+"" );

                    ftYieldList.add( ftYieldStatus );

                }
            }
        }
        catch ( Exception ex )
        {
            System.out.println("ERROR!!!ERROR!!! While Parsing the ASE SG FT Yield Report");
            ex.printStackTrace();
            throw ex;
        }

        return ftYieldList;
    }

    private void log( String message )
    {
        if ( message == null ) return;

        if ( m_parserLogger != null )
        {
            m_parserLogger.logDebug( message + LINE_SEPARATOR );
        }
        else
        {
            System.out.println( message );
        }
    } // end of log method
}

